import requests
from bs4 import BeautifulSoup
import pymysql

# 连接到MySQL数据库
connection = pymysql.connect(host='localhost',
                             user='root',
                             password='root',
                             db='22106101cdl',
                             charset='utf8',
                             cursorclass=pymysql.cursors.DictCursor)

# 爬取期刊论文信息的函数
def fetch_paper_info(url):
    response = requests.get(url)
    soup = BeautifulSoup(response.text, 'html.parser')

    title = soup.find('h1').text.strip()
    author_name = soup.find('div', class_='authors').text.strip() if soup.find('div', class_='authors') else ''
    abstract = soup.find('div', class_='abstract').text.strip() if soup.find('div', class_='abstract') else ''

    return {
        'title': title,
        'author_name': author_name,
        'abstract': abstract
    }

# 将论文信息插入到数据库的函数
def insert_paper_info(paper_info):
    with connection.cursor() as cursor:
        sql = "INSERT INTO papers (title, author_name, abstract) VALUES (%s, %s, %s)"
        cursor.execute(sql, (paper_info['title'], paper_info['author_name'], paper_info['abstract']))
    connection.commit()

# 爬取两篇论文的信息并插入到数据库中
url1 = 'http://cnki.kmlib.yn.cn/kcms/detail/detail.aspx?dbCode=cjfq&QueryID=46&CurRec=2&filename=JJYJ202110002&dbname=CJFDLAST2021'
url2 = 'http://cnki.kmlib.yn.cn/kcms/detail/detail.aspx?filename=FJSX201801002&dbcode=CJFQ&dbname=CJFD2018'

paper1_info = fetch_paper_info(url1)
insert_paper_info(paper1_info)

paper2_info = fetch_paper_info(url2)
insert_paper_info(paper2_info)

# 查询并打印存储的论文信息
with connection.cursor() as cursor:
    sql = "SELECT * FROM papers"
    cursor.execute(sql)
    result = cursor.fetchall()
    print(result)

# 关闭数据库连接
connection.close()
